package com.example.excel;

import com.example.translate.TransnTextTranslate;
import com.transn.sdk.util.Md5Util;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.xssf.usermodel.*;

import java.io.*;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * excel工具类
 * 读写excel
 *
 * @author 王振【wosen.wang@woordee.com】
 * @version 2019-09-14 12:38
 */
public class ExcelData {

    XSSFWorkbook wb = null;

    public ExcelData(File file) {
        try {
            InputStream inp = new FileInputStream(file);
            wb = new XSSFWorkbook(inp);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }catch (IOException e) {
            e.printStackTrace();
        }

    }

    /**
     * 取Excel所有数据，包含header
     *
     * @return List<String[]>
     */
    public List<String[]> getAllData(int sheetIndex) {
        List<String[]> dataList = new ArrayList<String[]>(100);
        try {
            int columnNum = 0;
            XSSFSheet sheet = wb.getSheetAt(sheetIndex);
            if (sheet.getRow(0) != null) {
                columnNum = sheet.getRow(0).getLastCellNum()
                        - sheet.getRow(0).getFirstCellNum();
            }
            if (columnNum > 0) {
                for(int rowNum = 0; rowNum <= sheet.getLastRowNum(); rowNum++){
                    XSSFRow row = sheet.getRow(rowNum);
                    String[] singleRow = new String[columnNum];
                    int n = 0;
                    for (int i = 0; i < columnNum; i++) {
                        XSSFCell cell = row.getCell(i, XSSFRow.CREATE_NULL_AS_BLANK);
                        switch (cell.getCellType()) {
                            case XSSFCell.CELL_TYPE_BLANK:
                                singleRow[n] = "";
                                break;
                            case XSSFCell.CELL_TYPE_BOOLEAN:
                                singleRow[n] = Boolean.toString(cell.getBooleanCellValue());
                                break;
                            // 数值
                            case XSSFCell.CELL_TYPE_NUMERIC:
                                if (DateUtil.isCellDateFormatted(cell)) {
                                    singleRow[n] = String.valueOf(cell.getDateCellValue());
                                } else {
                                    cell.setCellType(XSSFCell.CELL_TYPE_STRING);
                                    String temp = cell.getStringCellValue();
                                    // 判断是否包含小数点，如果不含小数点，则以字符串读取，如果含小数点，则转换为Double类型的字符串
                                    if (temp.contains(".")) {
                                        singleRow[n] = String.valueOf(new Double(temp)).trim();
                                    } else {
                                        singleRow[n] = temp.trim();
                                    }
                                }
                                break;
                            case XSSFCell.CELL_TYPE_STRING:
                                singleRow[n] = cell.getStringCellValue().trim();
                                break;
                            case XSSFCell.CELL_TYPE_ERROR:
                                singleRow[n] = "";
                                break;
                            case XSSFCell.CELL_TYPE_FORMULA:
                                cell.setCellType(XSSFCell.CELL_TYPE_STRING);
                                singleRow[n] = cell.getStringCellValue();
                                if (singleRow[n] != null) {
                                    singleRow[n] = singleRow[n].replaceAll("#N/A", "")
                                            .trim();
                                }
                                break;
                            default:
                                singleRow[n] = "";
                                break;
                        }
                        n++;
                    }
                    if ("".equals(singleRow[0])) {
                        continue;
                    }// 如果第一行为空，跳过
                    dataList.add(singleRow);
                }
            }
        }catch (Exception e){
            e.printStackTrace();
        }

        return dataList;
    }

    public static void export(String name, String[] titles, List<Map<String, String>> values, String out_path){

        // 第一步，创建一个HSSFWorkbook，对应一个Excel文件
        XSSFWorkbook hwb = new XSSFWorkbook();
        // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
        XSSFSheet sheet = hwb.createSheet(name);
        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
        XSSFRow row =sheet.createRow(0);
        // 第四步，创建单元格，并设置值表头 设置表头居中
        XSSFCellStyle style = hwb.createCellStyle();
        style.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式

        //声明列对象
        XSSFCell cell = null;

        //创建标题
        for(int i=0;i<titles.length;i++){
            cell = row.createCell(i);
            cell.setCellValue(titles[i]);
            cell.setCellStyle(style);
        }

        //创建内容
        for(int i=0;i<values.size();i++){
            row = sheet.createRow(i + 1);
            Map<String, String> value = values.get(i);
            int j = 0;
            for(String key:value.keySet()){
                //将内容按顺序赋给对应的列对象
                row.createCell(j).setCellValue(value.get(key));
                j++;
            }
        }

        //生成excel
        try{
            out_path += name + ".xlsx";
            FileOutputStream fout = new FileOutputStream(out_path);
            hwb.write(fout);
            fout.flush();
            fout.close();
        }catch (Exception e){

        }
    }

    /** 属性说明 excel标题名*/
    private static String[] titles = {"ID","TITLE","CONTENT", "PAGE_URL", "PICTURE_URL", "VIDEO_URL", "PUBLISH_SOURCE", "PUBLISH_SOURCE_URL",
            "PUBLISH_TIME", "PUBLISH_COUNT", "SITE_ID", "CATCH_TIME", "SUMMARY", "URLMD5", "CONTENT_TYPE", "IS_TRANS", "TRANS_TITLE",
            "TRANS_CONTENT", "BELONG_INDUSTRY", "BELONG_COMPANY", "ORDER_STATE"};

    /** 属性说明 导出路径*/
    private static String path = "D:/work/yq/";

    public static void main(String[] args){
        String filePath = path + "template.xlsx";
        File file = new File(filePath);
        // 获取时间数据
        ExcelData util = new ExcelData(file);
        List<String[]> list = util.getAllData(0);
        int id = 100001;
        SimpleDateFormat df = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
        List<Map<String, String>> dataList = new ArrayList<Map<String, String>>();
        for (int i = 0; i < list.size(); i++) {
            //第一行为标题
            if (i == 0)
                continue;

            Map<String, String> map = new LinkedHashMap<String, String>();
            String[] arr = list.get(i);
            map.put("ID", String.valueOf(id));
            String title = arr[0];
            String content = arr[1];
            map.put("TITLE", title);
            map.put("CONTENT", content);
            String pageUrl = arr[2];
            map.put("PAGE_URL", pageUrl);
            map.put("PICTURE_URL", "");
            map.put("VIDEO_URL", "");
            map.put("PUBLISH_SOURCE", arr[3]);
            map.put("PUBLISH_SOURCE_URL", "");
            String publishTime = arr[4];
            Date d = formatDateByZone(publishTime);
            map.put("PUBLISH_TIME", df.format(d));
            map.put("PUBLISH_COUNT", "");
            map.put("SITE_ID", "4");
            map.put("CATCH_TIME", df.format(new Date()));
            map.put("SUMMARY", "");
            map.put("URLMD5", Md5Util.encode(pageUrl));
            map.put("CONTENT_TYPE", "");
            map.put("IS_TRANS", "1");
            String srcLan = arr[5];
            String tarLan = "zh";
            String transTitle = TransnTextTranslate.machineTranslate(srcLan, tarLan, title);
            map.put("TRANS_TITLE", transTitle);
            String transContent = TransnTextTranslate.machineTranslate(srcLan, tarLan, content);
            map.put("TRANS_CONTENT", transContent);
            map.put("BELONG_INDUSTRY", ",161,");
            map.put("BELONG_COMPANY", ",167,");
            map.put("ORDER_STATE", "2");
            dataList.add(map);
            id++;
        }

        export("无线飞翔", titles, dataList, path);
    }

    /**
     * 函数功能说明 标准时区格式转换北京时间date
     */
    public static Date formatDateByZone(String time){
        SimpleDateFormat sdf = new SimpleDateFormat("EEE MMM dd HH:mm:ss zzz yyyy", Locale.US);
        Date date = null;
        try {
            date = (Date) sdf.parse(time);
        } catch (ParseException e) {
            e.printStackTrace();
        }
        return date;
    }
}
